package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import com.mysql.jdbc.StringUtils;
import cn.gson.crm.model.entity.Employee;
import cn.gson.crm.model.entity.StudentDetail;
import cn.gson.crm.model.po.StudentPayPo;

public class StudentDetailDao {
	// 每页三条记录
	private int pageSize = 3;
	/**
	 * 获取状态为status,缴费情况为pay
	 * @param status
	 * @param stuName
	 * @return
	 * @throws SQLException
	 */
	public List<StudentDetail> getStudentList(int page, String status, String isAllocation, String stuName, int isPay)
			throws SQLException {
		int start = (page - 1) * pageSize;
		String sql = "select * from(select * from (select * from student where stu_status = ? and is_allocation =? and grade_id>4 ";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}
		sql += ") as form  where form.is_allpay=?) as totalform join contract on contract.stu_id= totalform.id order by contract.create_time desc limit ?,?";
		params.add(isPay);
		params.add(start);
		params.add(pageSize);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StudentDetail> studentList = null;
		return buildStudentList(rows, studentList);
	}

	/**
	 * 通过学生id查询到收款记录
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public StudentDetail getPayRecord(String id) throws SQLException {
		String sql = "select *,SUM(this_pay)have_pay,(due_pay-SUM(this_pay))as no_pay, count(*)total from pay where stu_id=? ";
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, id);
		return buildStudentDetail(row);
	}

	/**
	 * 获取学生交款记录和根据日期查询收款记录
	 * @param page
	 * @param stuId
	 * @param payDate
	 * @return
	 * @throws SQLException
	 */
	public List<StudentDetail> getPayList(int page, String stuId, Date payDate) throws SQLException {
		String sql = "select * from pay where stu_id=?";
		List<Object> params = new ArrayList<>();
		params.add(stuId);
		if (payDate != null) {
			sql += " and pay_date=? ";
			params.add(payDate);
		}
		sql += " order by pay_date desc limit ?,?";
		params.add(page);
		params.add(pageSize);
		System.out.println(sql);
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StudentDetail> poList = new ArrayList<>();
		return buildStudentList(rows, poList);
	}

	/**
	 * 获取学生交款记录和根据日期查询收款记录总页数
	 * @param stuId
	 * @param payDate
	 * @return
	 * @throws SQLException
	 */
	public Integer getPayTotalPage(String stuId, Date payDate) throws SQLException {
		String sql = "select count(*)total from pay where stu_id=?";
		List<Object> params = new ArrayList<>();
		params.add(stuId);
		if (payDate != null) {
			sql += " and pay_date=? ";
			params.add(payDate);
		}
		Map<String, Object> row = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (row != null) {
			count = Integer.parseInt(row.get("total").toString());
		}
		return (int) Math.ceil(count.intValue() / pageSize);
	}
	
	/**
	 * 缴费
	 * 
	 */
	public Boolean createPayRecord(StudentDetail po) throws SQLException {
		String sql = "insert into pay(stu_id,emp_id,pay_id,due_pay,this_pay,pay_way) values(?,?,CONCAT('trkjStuPay',DATE_FORMAT(now(),'%Y%m%d'),LPAD(LAST_INSERT_ID()+1,20,'0')),?,?,?)";
		return Jdbc.getInstace().executeUpdate(sql, po.getId(), po.getEmpId(), po.getDuePay(), po.getThisPay(),
				po.getPayWay());
	}
	/**
	 * 获取状态为status,gradeId<5的学生
	 * 查询名字分页
	 * @param status
	 * @param page
	 * @param stuName
	 * @return
	 * @throws SQLException
	 */
	public List<StudentDetail> getNormalStudentList(String status,String stuName,String isAllocation) throws SQLException{
		String sql = "select * from (select * from student where stu_status = ? and is_allocation=?";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		if(!StringUtils.isNullOrEmpty(stuName)){
			sql+=" and stu_name like ?";
			params.add("%"+stuName.trim()+"%");
		}
		sql+=") as form where form.grade_id<5 order by form.grade_id desc,form.stu_createtime desc";
		List<Map<String, Object>> rows = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StudentDetail> studentList = null;
		return buildStudentList(rows,studentList);

	}

	
	/**
	 * 获取状态为status,交款情况为isAllPay，等级gradeId>4的学生总页数
	 * @param status
	 * @param isAllocation
	 * @param stuName
	 * @param gradeId
	 * @return
	 * @throws SQLException
	 */
	public int getTotalPage(String status, String isAllocation, String stuName, int isAllPay) throws SQLException {
		String sql = "select count(*)total from(select * from (select * from student where stu_status = ? and is_allocation =? and grade_id>4 ";
		List<Object> params = new ArrayList<>();
		params.add(status);
		params.add(isAllocation);
		if (!StringUtils.isNullOrEmpty(stuName)) {
			sql += " and stu_name like ?";
			params.add("%" + stuName.trim() + "%");
		}
		sql += ") as form  where form.is_allpay=?) as totalform join contract on contract.stu_id= totalform.id order by contract.create_time,totalform.grade_id,stu_createtime  desc ";
		params.add(isAllPay);
		Map<String, Object> data = Jdbc.getInstace().executeQuery(sql, params.toArray());
		Integer count = 0;
		if (data != null) {
			count = Integer.parseInt(data.get("total").toString());
		}
		return (int) Math.ceil(count.doubleValue() / pageSize);
	}

	/**
	 * 学生签约
	 * @param stuId
	 * @param empId
	 * @return
	 * @throws SQLException
	 */
	public Boolean getContract(StudentDetail po) throws SQLException {
		String sql = "insert into contract(contract_id,stu_id,emp_id) values(CONCAT('trkj',DATE_FORMAT(now(),'%Y%m%d'),LPAD(LAST_INSERT_ID()+1,20,'0')),?,?)";
		return Jdbc.getInstace().executeUpdate(sql, po.getId(), po.getEmpId());
	}
	
	/**
	 * 创建已签约学员的收款记录
	 * @throws SQLException
	 */
	public boolean Createpayandsign(StudentDetail studentdetail) throws SQLException {
		String sql = "insert into pay(stu_id,due_pay,have_pay,this_pay,no_pay,emp_id,pay_date)"
				+ "values(?,?,?,?,?,?)";
		boolean judge = Jdbc.getInstace().executeUpdate(sql, studentdetail.getId(), studentdetail.getDuePay(),
				studentdetail.getHavePay(),studentdetail.getThisPay(),studentdetail.getNoPay(), studentdetail.getEmpId(),
				studentdetail.getPayDate());
		return judge;
	}
	
	/**
	 * 给已签约且未缴清学员续费
	 * @throws SQLException 
	 */
	public boolean ContinuePay(StudentDetail continuepay) throws SQLException{
		String sql = "update pay set due_pay,have_pay,this_pay,no_pay,emp_id,pay_date where stu_id = ?";
		boolean judge = Jdbc.getInstace().executeUpdate(sql, continuepay.getDuePay(),continuepay.getHavePay(),continuepay.getThisPay(),continuepay.getNoPay(),continuepay.getEmpId(),continuepay.getPayDate());
		return judge;
		
	}
	/**
	 * 改变学员的交款状态F
	 * @param po
	 * @return
	 * @throws SQLException
	 */
	public Boolean changePayStatus(StudentDetail po) throws SQLException {
		String sql = "update student set is_allpay=? where id=?";
		return Jdbc.getInstace().executeUpdate(sql, po.getIs_allpay(), po.getId());
	}
	
	/**
	 * 改变学生等级
	 * @param stuId
	 * @return
	 * @throws SQLException
	 */
	public Boolean changeStuGrade(String gradeId, String stuId) throws SQLException {
		String sql = "update student set grade_id=? where id=?;";
		return Jdbc.getInstace().executeUpdate(sql, gradeId, stuId);
	}
	/**
	 * 取到集合之后再进行分页
	 * @param request
	 * @param list
	 * @param pageSize
	 * @param page
	 * @return
	 */
	public List<StudentDetail> getListThenDividPage(HttpServletRequest request,List<StudentDetail> list,int page){
		List<StudentDetail> subList = null;
		Integer totalCount = list.size();
		/* 计算出总共能分成多少页 */
		int pageCount = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
		if (totalCount > 0) {
			if (page <= pageCount) {
				// 当前页数为第一页
				if (page == 1) {
					// 数据总数小于每页显示的数据条数
					if (totalCount <= pageSize) {
						// 截止到总的数据条数(当前数据不足一页，按一页显示)，这样才不会出现数组越界异常
						subList = list.subList(0, totalCount);
					} else {
						subList = list.subList(0, pageSize);
					}
				} else {
					// 截取起始下标
					int fromIndex = (page - 1) * pageSize;
					// 截取截止下标
					int toIndex = page * pageSize;
					/* 计算截取截止下标 */
					if ((totalCount - toIndex) % pageSize >= 0) {
						toIndex = page * pageSize;
					} else {
						toIndex = (page - 1) * pageSize + (totalCount % pageSize);
					}
					if (totalCount >= toIndex) {
						subList = list.subList(fromIndex, toIndex);
					}
				}
			} else {
				subList = null;
			}
	}
		request.setAttribute("totalPage", pageCount);
		return subList;
		
	}
	
	/**
	 * 根据学生id查询学生信息
	 * @param stuId
	 * @return
	 * @throws SQLException 
	 */
	public StudentDetail getStudentInfo(String stuId) throws SQLException{
		String sql= "select * from student  join allocation  where id = ?";
		Map<String,Object> row = Jdbc.getInstace().executeQuery(sql, stuId);
		return buildStudentDetail(row);

	}

	/**
	 * 组装学员信息列表
	 * @param dataList
	 * @return
	 * @throws SQLException
	 */
	private List<StudentDetail> buildStudentList(List<Map<String, Object>> rows, List<StudentDetail> studentdetailList)
			throws NumberFormatException, SQLException {
		studentdetailList = new ArrayList<>();
		if (rows != null) {
			for (Map<String, Object> row : rows) {
				studentdetailList.add(buildStudentDetail(row));
			}
		}
		return studentdetailList;
	}

	/**
	 * 组装学生信息
	 * @param data
	 * @return
	 */
	private StudentDetail buildStudentDetail(Map<String, Object> data) throws SQLException {
		StudentDetail po = null;
		if (data != null) {
			po = new StudentDetail();
			if (data.get("id") != null) {
				po.setId(Integer.parseInt(data.get("id").toString()));
			}
			po.setIsAllocation((String) data.get("is_allocation"));
			po.setName((String) data.get("stu_name"));
			po.setGender((String) data.get("stu_gender"));
			po.setSource((String) data.get("stu_source"));
			po.setEducation((String) data.get("stu_education"));
			po.setPhone((String) data.get("stu_phone"));
			po.setAddress((String) data.get("stu_address"));
			po.setIdCard((String) data.get("stu_idcard"));
			po.setQq((String) data.get("stu_qq"));
			po.setFatherName((String) data.get("stu_fathername"));
			po.setFatherTel((String) data.get("stu_fathertel"));
			po.setMotherName((String) data.get("stu_mothername"));
			po.setMotherTel((String) data.get("stu_mothertel"));
			po.setCreator((String) data.get("stu_creator"));

			po.setCreateTime((Date) data.get("stu_createtime"));
			if (data.get("grade_id") != null) {
				po.setGradeId(Integer.parseInt(data.get("grade_id").toString()));
			}
			if (data.get("counselor_id") != null) {
				po.setCounselorId(Integer.parseInt(data.get("counselor_id").toString()));
				// 获取咨询师姓名
				Employee emp = new EmploDao().emploGetById("正常", Integer.parseInt(data.get("counselor_id").toString()));
				if (emp != null) {
					po.setCounselorName(emp.getUserName());
				}
			}
			po.setStatus((String) data.get("stu_status"));
		}
			if (data.get("p_id") != null) {
				po.setPid(Integer.parseInt(data.get("p_id").toString()));
			}
			po.setPayOrderId((String) data.get("pay_id"));
			if (data.get("emp_id") != null) {
				po.setEmpId(Integer.parseInt(data.get("emp_id").toString()));
			}
			if (data.get("due_pay") != null) {
				po.setDuePay(Integer.parseInt(data.get("due_pay").toString()));
			}

			if (data.get("thisPay") != null) {
				po.setThisPay(Integer.parseInt(data.get("thisPay").toString()));
			}
			po.setPayDate((Date) data.get("pay_date"));
			if (data.get("no_pay") != null) {
				po.setNoPay(Integer.parseInt(data.get("no_pay").toString()));
			}
			if (data.get("have_pay") != null) {
				po.setHavePay(Integer.parseInt(data.get("have_pay").toString()));
			}
			if (data.get("cid") != null) {
				po.setCid(Integer.parseInt(data.get("c_id").toString()));

			}
			po.setContactOrderId((String) data.get("contract_id"));
			po.setContractDate((Date) (data.get("contract_createtime")));
			if (data.get("pay_way") != null) {
				if (1 == Integer.parseInt(data.get("pay_way").toString())) {
					po.setPayWay("现清");
				}
				if (2 == Integer.parseInt(data.get("pay_way").toString())) {
					po.setPayWay("贷款");
				}
			}	

			if (data.get("class_type") != null) {
				po.setClassType(Integer.parseInt(data.get("class_type").toString()));
			}
			return po;
		}

	}